//202102104015柴亚楠
package cn.edu.sdjzu.xg.kcsj.dao;

import util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public final class CommonDao {
    private static CommonDao commonDao = new CommonDao();

    private CommonDao() {
    }

    public static CommonDao getInstance() {
        return commonDao;
    }

    /**
     * 返回指定表的元素数量
     * @param connection
     * @param tableName 表名
     * @return 元组数量
     * @throws SQLException
     */
    public int count(Connection connection, String tableName) throws SQLException {
        String sql_count;
        sql_count = "SELECT COUNT(id) AS cnt FROM " + tableName;
        PreparedStatement pstmt_count =
                connection.prepareStatement(sql_count);
        int counter = 0;
        ResultSet resultSet_count = pstmt_count.executeQuery();
        if (resultSet_count.next()) {
            counter = resultSet_count.getInt("cnt");
        }
        JdbcHelper.close(resultSet_count, pstmt_count);
        return counter;
    }

    /**
     * 返回指定表的指定外键下的元素数量
     * @author gty2001128@163.com
     * @param connection
     * @param tableName 表名
     * @param foreignKey 外键名
     * @param foreignKey_int 外键值
     * @return 元组数量
     * @throws SQLException
     */

    public int countWhereForeignKey(Connection connection, String tableName, String  foreignKey , int foreignKey_int) throws SQLException {
        String sql_count = "SELECT COUNT(id) as cnt FROM " + tableName + " WHERE " + foreignKey + " = " + foreignKey_int;
        PreparedStatement pstmt_count = connection.prepareStatement(sql_count);
        int counter = 0;
        ResultSet resultSet_count = pstmt_count.executeQuery();
        if (resultSet_count.next()) {
            counter = resultSet_count.getInt("cnt");
        }
        JdbcHelper.close(resultSet_count, pstmt_count);
        return counter;
    }
}

